﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;

namespace UdrinkWater_Project.Admin
{
    public partial class Calculate_Salary : System.Web.UI.Page
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        CheckTime CH = new CheckTime();
        string ChYear = DateTime.Now.ToString("yyyy", new System.Globalization.CultureInfo("en-US"));
        string ChMonth = DateTime.Now.ToString("MM", new System.Globalization.CultureInfo("th-TH"));
        string ChDay = DateTime.Now.ToString("dd", new System.Globalization.CultureInfo("th-TH"));
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void bt_CalculateAndExport_Click(object sender, EventArgs e)
        {
           // Calculate();
            if ((ChMonth == "01") || (ChMonth == "03") || (ChMonth == "05") || (ChMonth == "07") || (ChMonth == "08") || (ChMonth == "10") || (ChMonth == "12"))
            {
                if (ChDay == "31")
                {
                    Calculate();  
                }
            }
            else if ((ChMonth == "04") || (ChMonth == "06") || (ChMonth == "09") || (ChMonth == "11"))
            {
                if (ChDay == "30")
                {
                    Calculate();
                }
            }
            else
            {
                DataTable dt = new DataTable();
                dt = CH.CheckDayOfYear(ChYear);
                string CheckYear = dt.Rows[0]["DayCount"].ToString();
                if (CheckYear=="365")
                {
                    if (ChDay == "28")
                    {
                        Calculate();
                    }
                }
                if (CheckYear == "366")
                {
                    if (ChDay == "29")
                    {
                        Calculate();
                    }
                }
            }
            pc_Salary.ShowOnPageLoad = true;
            
        }
        public void Calculate()
        {
            DataTable CheckCal = CH.GetData_CheckCalculate();
            if (CheckCal.Rows.Count == 0)
            {
                Data_Salary Data = new Data_Salary();
                DataTable CheckEmp = CH.GetData_Employee();

                DataTable RateOT = GetData();
                int RateOver = Convert.ToInt32(RateOT.Rows[0]["RateOT_Num"].ToString());
                for (int i = 0; i < CheckEmp.Rows.Count; i++)
                {
                    Data.Salary_OT = 0;
                    Data.Salary_Commission = 0;
                    string Employee = CheckEmp.Rows[i]["ID_Card"].ToString();
                    Data.Employee = Employee;
                    DataTable OT = CH.GetData_OT(Employee);
                    //คำนวณ โอที
                    for (int j = 0; j < OT.Rows.Count; j++)
                    {
                        string CheckIn = OT.Rows[j]["CheckIn_Time"].ToString();
                        string CheckOut = OT.Rows[j]["CheckOut_Time"].ToString();

                        TimeSpan Tp = Convert.ToDateTime(CheckOut) - Convert.ToDateTime(CheckIn);
                        var Hour = Tp.Hours;
                        var Minue = Tp.Minutes;
                        int H = Hour * RateOver;

                        if ((Minue) >= 20 && (Minue) <= 50)
                        {
                            Data.Salary_OT += H + (RateOver / 2);
                        }
                        if (Minue < 20)
                        {
                            Data.Salary_OT += H;
                        }
                        if (Minue > 50)
                        {
                            Data.Salary_OT += H + RateOver;
                        }
                    }
                    //คำนวณเวลาขาดงาน
                    DataTable Absence = CH.GetData_Reduce_Absence(Employee);
                    DataTable RateAb = GetDataAbsence();
                    int RateAbsence = Convert.ToInt32(RateAb.Rows[0]["Rate_AbsenceNum"].ToString());

                    Data.Salary_Absence = Absence.Rows.Count * RateAbsence; 
                    
                    //คำนวณเบิกจ่ายล่วงหน้า
                    DataTable WithDraw = CH.GetData_Reduce_Withdraw(Employee);
                    if (WithDraw.Rows[0]["TotalEmp"].ToString()!= "")
                    {
                       Data.Salary_WithDraw = Convert.ToInt32(WithDraw.Rows[0]["TotalEmp"].ToString()); 
                    }
                    else
                    {
                        Data.Salary_WithDraw = 0;
                    }
                    
                    //คำนวณค่าคอมมิสชั่น
                    DataTable Com1 = CH.GetData_Reduce_Commission(Employee, 1);//ถัง
                    DataTable Com2 = CH.GetData_Reduce_Commission(Employee, 2);//ลัง
                    DataTable Com3 = CH.GetData_Reduce_Commission(Employee, 3);//แพ็ค
                    DataTable Com4 = CH.GetData_Reduce_Commission(Employee, 4);//โหล
                    string ComTank = Com1.Rows[0]["TOTAL"].ToString(); //ถัง
                    string ComCrate = Com2.Rows[0]["TOTAL"].ToString(); //ลัง
                    string Compack = Com3.Rows[0]["TOTAL"].ToString(); //แพ็ค
                    string Comdozen = Com4.Rows[0]["TOTAL"].ToString(); //โหล

                    DataTable Ratetub = GetDataTub();
                    int Tub = Convert.ToInt32(Ratetub.Rows[0]["Rate_TubNum"].ToString());

                    DataTable RatePet = GetDataPet();
                    int Pet = Convert.ToInt32(RatePet.Rows[0]["Rate_PackNum"].ToString());

                    DataTable RateCrate = GetDataCrate();
                    int Crate = Convert.ToInt32(RateCrate.Rows[0]["Rate_CrateNum"].ToString());

                    DataTable RateOneWay = GetDataOneWay();
                    int OneWay = Convert.ToInt32(RateOneWay.Rows[0]["Rate_OneWay_Num"].ToString());

                    if (ComTank != "")//ถัง
                    {
                        Data.Salary_Commission += Convert.ToDouble(ComTank) * Tub;
                    }
                    else
                    {
                        Data.Salary_Commission += 0;
                    }
                    if (ComCrate != "")//ลัง
                    {
                        Data.Salary_Commission += Convert.ToDouble(ComCrate) * Crate;
                    }
                    else
                    {
                        Data.Salary_Commission += 0;
                    }
                    if (Compack != "")//แพ็ค
                    {
                        Data.Salary_Commission += Convert.ToDouble(Compack) * Pet;
                    }
                    else
                    {
                        Data.Salary_Commission += 0;
                    }
                    if (Comdozen != "")//โหล
                    {
                        Data.Salary_Commission += (Convert.ToDouble(Comdozen) / 2) * OneWay;
                    }
                    else
                    {
                        Data.Salary_Commission += 0;
                    }

                    //คำนวณค่าอื่นๆ
                    DataTable RateOther = GetDataOther();
                    int Rate = Convert.ToInt32(RateOther.Rows[0]["Rate_OtherNum"].ToString());
                    Data.Salary_Other = Rate;
                    Inserts(Data,ChMonth,ChYear);
                }

            }
            
        }
        public DataTable GetDataTub()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [Rate_TubDate],[Rate_TubNum] FROM [TB_RateTub] ORDER BY [Rate_TubDate] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetDataPet()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [Rate_PackDate],[Rate_PackNum] FROM [TB_Rate_Pack] ORDER BY [Rate_PackDate] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetDataCrate()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [Rate_CrateDate],[Rate_CrateNum] FROM [TB_RateCrate] ORDER BY [Rate_CrateDate] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetDataOneWay()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [Rate_OneWay_Date],[Rate_OneWay_Num] FROM [TB_RateOneWay] ORDER BY [Rate_OneWay_Date] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [RateOT_Date],[RateOT_Num] FROM [TB_RateOT] ORDER BY [RateOT_Date] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetDataAbsence()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [Rate_AbsenceDate],[Rate_AbsenceNum] FROM [TB_RateAbsence] ORDER BY [Rate_AbsenceDate] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetDataOther()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TOP 1 [Rate_OtherDate],[Rate_OtherNum] FROM [TB_RateOther] ORDER BY [Rate_OtherDate] DESC";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public static object GetDataValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }

            return value;
        }
        public void Inserts(Data_Salary Data,string Month,string Year)
        {
            string sqltext = @"insert into TB_Salary 
                                   ( [Salary_Doc]
                                    ,[Employee_ID]
                                    ,[Salary_Start]
                                    ,[Salary_End]
                                    ,[Salary_OT]
                                    ,[Salary_Absence]
                                    ,[Salary_WithDraw]
                                    ,[Salary_Other]
                                    ,[Salary_Commission])
                                   values (@Salary_Doc,@Employee_ID,@Salary_Start,@Salary_End,@Salary_OT,@Salary_Absence,@Salary_WithDraw,@Salary_Other,@Salary_Commission)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            sqlcomm.Parameters.AddWithValue("@Salary_Doc", GetDataValue(Data.Employee+DateTime.Now));
            sqlcomm.Parameters.AddWithValue("@Employee_ID", GetDataValue(Data.Employee));
            string Date = Year + "/" + Month + "/01";
            string DateNow = Convert.ToDateTime(Date).ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH"));
            sqlcomm.Parameters.AddWithValue("@Salary_Start", GetDataValue(DateNow));
            sqlcomm.Parameters.AddWithValue("@Salary_End", GetDataValue(DateNow));
            sqlcomm.Parameters.AddWithValue("@Salary_OT", GetDataValue(Data.Salary_OT));
            sqlcomm.Parameters.AddWithValue("@Salary_Absence", GetDataValue(Data.Salary_Absence));
            sqlcomm.Parameters.AddWithValue("@Salary_WithDraw", GetDataValue(Data.Salary_WithDraw));
            sqlcomm.Parameters.AddWithValue("@Salary_Other", GetDataValue(Data.Salary_Other));
            sqlcomm.Parameters.AddWithValue("@Salary_Commission", GetDataValue(Data.Salary_Commission));
            
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }

        protected void btOK_Click(object sender, EventArgs e)
        {
            string Mounth = Cb_Month.Value.ToString();
            string Year = (Convert.ToInt32(Cb_Year.Value.ToString()) - 543).ToString();
            SqlConnection objConn = new SqlConnection();
            SqlCommand objCmd = new SqlCommand();
            SqlDataAdapter dtAdapter = new SqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt = null;
            string strConnString = null;
            StringBuilder strSQL = new StringBuilder();

            strSQL.Append(" SELECT TB_Employee.ID_Card,TB_Employee.Prefix,TB_Employee.FirstName,TB_Employee.LastName,TB_Department.Department_Name,");
            strSQL.Append(" TB_Employee.Salary,TB_Salary.Salary_Absence,TB_Salary.Salary_WithDraw,TB_Salary.Salary_Other,TB_Salary.Salary_Commission,TB_Salary.Salary_OT ");
            strSQL.Append(" FROM TB_Employee  ");
            strSQL.Append(" LEFT JOIN TB_Department ON TB_Employee.Department_ID = TB_Department.Department_ID ");
            strSQL.Append(" LEFT JOIN TB_Salary ON TB_Salary.Employee_ID = TB_Employee.ID_Card ");
            strSQL.Append(" WHERE TB_Salary.Salary_Start = '"+Year+"-"+Mounth+"-01' ");

            objConn.ConnectionString = Connection;
            var _with1 = objCmd;
            _with1.Connection = objConn;
            _with1.CommandText = strSQL.ToString();
            _with1.CommandType = CommandType.Text;
            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds, "ReportSalary");
            dt = ds.Tables[0];

            dtAdapter = null;
            objConn.Close();
            objConn = null;

            ReportDocument rpt = new ReportDocument();
            rpt.Load(Server.MapPath("~/Admin/ReportSal.rpt"));

            rpt.SetDataSource(dt);
            rpt.SetParameterValue("Month", Cb_Month.SelectedItem.Text);
            rpt.SetParameterValue("Year", Cb_Year.Value);
            //this.CrystalReportViewer1.ReportSource = rpt;

            rpt.ExportToHttpResponse
            (CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "SlipSalary");
        }

       
    }
}